import pandas as pd

path = r'D:\Desktop\\新建文件夹\test.xlsx'
data = pd.read_excel(path)
table_name = 'table_name '

columns = ', '.join(data.columns)

with open('insert_statements.txt', 'w', encoding='utf-8') as f:
    insert_statements = []
    for index, row in data.iterrows():
        valuesList = []
        for col_name, value in row.items():
            if pd.isna(value):
                value = 'NULL'
                valuesList.append(value)
            else:
                if isinstance(value, (int, float)):
                    # 如果是数值类型
                    value = str(value)
                    valuesList.append(str(value))
                else:
                    # 如果是字符串类型
                    value = str(value).strip()
                    value = value.replace("'", "\\'").replace("\r\n", " ").replace("\n", " ")
                    value = f"'{value}'"
                    valuesList.append(value)
        values = ', '.join(valuesList)
        insert_statement = f"INSERT INTO {table_name} ({columns}) VALUES ({values});"
        insert_statements.append(insert_statement)
        f.writelines(insert_statement)

# 打印出来看看效果
for statement in insert_statements:
    print(statement)

    # 定义INSERT语句的模板
    insert_template = "INSERT INTO students (name, age, score) VALUES ('%s', %d, %.2f)"

    # 学生的信息
    name = "Alice"
    age = 18
    score = 95.5

    # 将变量的值填充到模板中
    insert_statement = insert_template % (name, age, score)

    # 打印生成的INSERT语句
    print(insert_statement)

